from typing import Any

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, select, Date, delete
from sqlalchemy.orm import sessionmaker, relationship, as_declarative, contains_eager, joinedload
import models


@as_declarative()
class Base:
    # Базовый класс для объектов User и Task
    pass


class Cabinet(Base):
    __tablename__ = 'cabinet'
    id = Column(Integer, primary_key=True, nullable=False)
    name = Column(String, nullable=False, unique=True)
    entries = relationship('Entry', cascade='all, delete', back_populates="cabinet")

    def __init__(self, id=None, name=None):
        self.id = id
        self.name = name

    def to_model(self) -> models.Cabinet:
        return models.Cabinet(id=self.id, name=self.name)


class Direction(Base):
    __tablename__ = 'direction'
    id = Column(Integer, primary_key=True, nullable=False)
    name = Column(String, nullable=False, unique=True)
    students = relationship('Student', cascade='all, delete', back_populates='direction')

    def __init__(self, id=None, name=None):
        self.id = id
        self.name = name

    def to_model(self) -> models.Direction:
        return models.Direction(id=self.id, name=self.name)


class Laborant(Base):
    __tablename__ = 'laborant'
    id = Column(Integer, primary_key=True, nullable=False)
    second_name = Column(String, nullable=False)
    entries = relationship('Entry', cascade='all, delete', back_populates='laborant')

    def __init__(self, id=None, second_name=None):
        self.id = id
        self.second_name = second_name

    def to_model(self) -> models.Laborant:
        return models.Laborant(id=self.id, second_name=self.second_name)


def laborant_from_model(model: models.Laborant) -> Laborant:
    return Laborant(id=model.id, second_name=model.second_name)


class Student(Base):
    __tablename__ = 'student'
    id = Column(Integer, primary_key=True, nullable=False)
    name = Column(String, nullable=False)
    direction_id = Column(Integer, ForeignKey('direction.id'), nullable=False)
    direction = relationship("Direction")
    entries = relationship('Entry', cascade='all, delete', back_populates='student')

    def __init__(self, id=None, name=None, direction_id=None):
        self.id = id
        self.name = name
        self.direction_id = direction_id

    def to_model(self) -> models.Student:
        return models.Student(id=self.id, name=self.name, direction=self.direction.to_model())


class Entry(Base):
    __tablename__ = 'entry'
    id = Column(Integer, primary_key=True, nullable=False)
    cabinet_id = Column(Integer, ForeignKey('cabinet.id'), nullable=False)
    cabinet = relationship("Cabinet", back_populates='entries')
    student_id = Column(Integer, ForeignKey('student.id'), nullable=False)
    student = relationship("Student", back_populates='entries')
    laborant_id = Column(Integer, ForeignKey('laborant.id'), nullable=False)
    laborant = relationship("Laborant", back_populates='entries')
    comp_num = Column(Integer, nullable=False)
    lesson_num = Column(Integer, nullable=False)
    lesson_date = Column(Date, nullable=False)

    def __init__(self, id=None, cabinet_id=None, student_id=None, laborant_id=None, comp_num=None, lesson_num=None,
                 lesson_date=None):
        self.id = id
        self.student_id = student_id
        self.cabinet_id = cabinet_id
        self.laborant_id = laborant_id
        self.lesson_date = lesson_date
        self.lesson_num = lesson_num
        self.comp_num = comp_num

    def to_model(self) -> models.Entry:
        return models.Entry(id=self.id, cabinet=self.cabinet.to_model(), student=self.student.to_model(),
                            laborant=self.laborant.to_model(), comp_num=self.comp_num, lesson_num=self.lesson_num,
                            lesson_date=self.lesson_date)


# url: postgresql://postgres:postgres@localhost:5432/journal
class ControllerDB:
    def __init__(self, url: str):
        self.url = url
        self.engine = create_engine(url)
        self.session = sessionmaker(bind=self.engine)
        Base.metadata.create_all(self.engine)

    def get_page(self, obj, page: int = 1, size: int = 10):
        page = max(page, 1)
        s = self.session()
        content = s.query(obj).limit(size).offset((page - 1) * size).all()
        total = s.query(obj).count()
        if total % size == 0:
            total_pages = total // size
        else:
            total_pages = total // size + 1
        content = list(map(lambda m: m.to_model(), content))
        return models.Page(content, page, total_pages, size)

    # Get page
    def get_directions_page(self, page: int = 1, size: int = 10) -> models.Page:
        return self.get_page(Direction, page, size)

    def get_cabinets_page(self, page: int = 1, size: int = 10) -> models.Page:
        return self.get_page(Cabinet, page, size)

    def get_laborants_page(self, page: int = 1, size: int = 10) -> models.Page:
        return self.get_page(Laborant, page, size)

    def get_students_page(self, page: int = 1, size: int = 10) -> models.Page:
        return self.get_page(Student, page, size)

    def get_entries_page(self, page: int = 1, size: int = 10) -> models.Page:
        return self.get_page(Entry, page, size)

    def get_one(self, obj, id: int) -> Any:
        return self.get_one_db_obj(obj, id).to_model()

    def get_one_db_obj(self, obj, id: int):
        s = self.session()
        ans = s.query(obj).filter_by(id=id).one()
        return ans

    def get_all_db_obj(self, obj) -> list:
        s = self.session()
        ans = s.query(obj).all()
        return ans

    def get_all(self, obj):
        s = self.session()
        ans = s.query(obj).all()
        ms = list(map(lambda m: m.to_model(), ans))
        s.close()
        return ms

    def get_all_directions(self) -> list:
        return self.get_all(Direction)

    def get_all_cabinets(self) -> list:
        return self.get_all(Cabinet)

    def get_all_laborants(self) -> list:
        return self.get_all(Laborant)

    def get_all_students(self) -> list:
        return self.get_all(Student)

    def get_all_entries(self) -> list:
        return self.get_all(Entry)

    # Get one
    def get_direction(self, id: int) -> models.Direction:
        return self.get_one(Direction, id)

    def get_cabinet(self, id: int) -> models.Cabinet:
        return self.get_one(Cabinet, id)

    def get_laborant(self, id: int) -> models.Laborant:
        return self.get_one(Laborant, id)

    def get_student(self, id: int) -> models.Student:
        return self.get_one(Student, id)

    def get_entry(self, id: int) -> models.Entry:
        return self.get_one(Entry, id)

    def update(self, obj, model):
        s = self.session()
        db_obj = self.get_one_db_obj(obj, model.id)
        for k, v in model:
            setattr(db_obj, k, v)
        s.merge(db_obj)
        s.commit()
        s.refresh(db_obj)
        m = db_obj.to_model()
        s.close()
        return m

    # Update
    def update_direction(self, direction: models.Direction) -> models.Direction:
        return self.update(Direction, direction)

    def update_cabinet(self, cabinet: models.Cabinet) -> models.Cabinet:
        return self.update(Cabinet, cabinet)

    def update_laborant(self, laborant: models.Laborant) -> models.Laborant:
        return self.update(Laborant, laborant)

    def update_student(self, student: models.Student) -> models.Student:
        return self.update(Student, student)

    def update_entry(self, entry: models.Entry) -> models.Entry:
        return self.update(Entry, entry)

    def delete(self, obj, id: int):
        s = self.session()
        obj = self.get_one_db_obj(obj, id)
        obj = s.merge(obj)
        s.delete(obj)
        s.commit()
        s.close()

    # Delete
    def delete_direction(self, id: int) -> None:
        self.delete(Direction, id)

    def delete_cabinet(self, id: int) -> None:
        self.delete(Cabinet, id)

    def delete_laborant(self, id: int) -> None:
        self.delete(Laborant, id)

    def delete_student(self, id: int) -> None:
        self.delete(Student, id)

    def delete_entry(self, id: int) -> None:
        self.delete(Entry, id)

    # create
    def create_direction(self, direction: models.Direction) -> models.Direction:
        s = self.session()
        db_obj = Direction(name=direction.name)
        s.add(db_obj)
        s.commit()
        s.refresh(db_obj)
        return db_obj.to_model()

    def create_cabinet(self, cabinet: models.Cabinet) -> models.Cabinet:
        s = self.session()
        db_obj = Cabinet(name=cabinet.name)
        s.add(db_obj)
        s.commit()
        s.refresh(db_obj)
        return db_obj.to_model()

    def create_laborant(self, laborant: models.Laborant) -> models.Laborant:
        s = self.session()
        db_obj = Laborant(second_name=laborant.second_name)
        s.add(db_obj)
        s.commit()
        s.refresh(db_obj)
        return db_obj.to_model()

    def create_student(self, student: models.Student) -> models.Student:
        s = self.session()
        db_obj = Student(name=student.name, direction_id=student.direction.id)
        s.add(db_obj)
        s.commit()
        s.refresh(db_obj)
        return db_obj.to_model()

    def create_entry(self, entry: models.Entry) -> models.Entry:
        s = self.session()
        db_obj = Entry(student_id=entry.student.id, cabinet_id=entry.cabinet.id, laborant_id=entry.laborant.id,
                       comp_num=entry.comp_num, lesson_num=entry.lesson_num, lesson_date=entry.lesson_date)
        s.add(db_obj)
        s.commit()
        s.refresh(db_obj)
        return db_obj.to_model()
